package wang.Seacher.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * mysql数据库连接类
 * @名称: MySQLConnect.java
 * @包名： com.wang.util
 * @描述: TODO
 * @author： wangqiang
 * @日期：2016-7-16 下午4:20:55
 */
public class MySQLConnect {

	/** 数据库名 **/
	private static final String dbName = "test";
	/** 用户名 **/
	private static final String user = "root";
	/** 密码 **/
	private static final String password = "1234";
	/** 数据库链接地址 **/
	private static final String url = "jdbc:mysql://localhost:3306/" + dbName + "?useEncoding=true&charasetEccoding=utf-8";

	public static void main(String[] args) {
		MySQLConnect db = new MySQLConnect();
		DateTimeUtil dt = new DateTimeUtil();

		//验证插入语句
		Map<String, String> map = new HashMap<String, String>();
		map.put("batch_no", "6666");
		map.put("create_time", dt.getDateTime24());
		map.put("modify_time", dt.getDateTime24());
		db.insert("seven", map);

		//验证修改语句
//		Map<String, String> map = new HashMap<String, String>();
//		map.put("batch_no", "6666");
//		map.put("create_time", dt.getDateTime24());
//		map.put("modify_time", dt.getDateTime24());
//		Map<String, String> conditionMap = new HashMap<String, String>();
//		conditionMap.put("id", "2");
//		db.update("alipay", map, conditionMap);

		//验证查询语句
		String tableName = "seven";
		List<String> targetNameList = new ArrayList<>();
		targetNameList.add("id");
		targetNameList.add("batch_no");
		targetNameList.add("create_time");
		targetNameList.add("modify_time");

		Map<String, String> conditionMap = new HashMap<>();
		conditionMap.put("id", "2");
		Map<String, String> resultMap = db.select(tableName, targetNameList, conditionMap);
		System.out.println(resultMap);
	}

	/**
	 * 获取数据库连接
	 * 
	 * @author： wangqiang
	 * @日期：2016-5-19 下午8:03:12
	 * @return
	 */
	public static Connection getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(url, user, password);
			return conn;
		} catch (ClassNotFoundException e) {
			System.out.println("-----------找不到jdbc驱动-----------");
			e.printStackTrace();
			return null;
		} catch (SQLException e) {
			System.out.println("-----------创建数据库连接出错-----------");
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * <p>插入语句</p>
	 * 
	 * @author： wangqiang
	 * @日期：2016-5-19 下午8:09:49
	 * @param tableName 表名
	 * @param filedName 字段名
	 * @param values 字段值
	 * @return
	 */
	public int insert(String tableName, Map<String, String> map) {
		String filedName = "";
		String values = "";
		Set<String> key = map.keySet();
		Iterator<String> it = key.iterator();
		int i = 0;
		String keyname = "";
		while (it.hasNext()) {
			keyname = it.next();
			if (i == 0) {
				filedName = filedName + keyname;
				values = values + "'" + map.get(keyname);
				i++;
				continue;
			}
			filedName = filedName + "," + keyname;
			values = values + "','" + map.get(keyname);
		}
		values = values + "'";
		String sql = "insert into " + tableName + "( " + filedName + ") values (" + values + ")";
		Connection conn = MySQLConnect.getConnection();
		try {
			Statement st = conn.createStatement();
			if (filedName.equals("") || values.equals("")) {
				return 0;
			}
			int count = st.executeUpdate(sql);
			return count;
		} catch (SQLException e) {
			System.out.println("-----------创建Statements出错-----------");
			e.printStackTrace();
			return 0;
		}
	}

	/**
	 * <p>数据库更新操作</p>
	 * 
	 * @author： wangqiang
	 * @日期：2016-5-20 上午10:52:37
	 * @param tableName 表名
	 * @param modifyMap 更新内容
	 * @param conditionMap 更新条件
	 * @return
	 */
	public int update(String tableName, Map<String, String> modifyMap, Map<String, String> conditionMap) {

		//定义sql语句
		//由于要不断的对sql字符串进行更改,所以使用StringBuilder以提高性能
		StringBuilder sql = new StringBuilder();
		sql.append("update " + tableName);

		//将更新的内容放入sql语句
		Set<String> modifySet = modifyMap.keySet();
		Iterator<String> modifyIt = modifySet.iterator();
		int i = 0;
		while (modifyIt.hasNext()) {
			String name = modifyIt.next();
			if (i == 0) {
				sql.append(" set " + name + " = '" + modifyMap.get(name) + "'");
				i++;
				continue;
			}
			sql.append(" , " + name + " = '" + modifyMap.get(name) + "'");
		}

		//将更新的条件放入sql语句
		Set<String> conditionSet = conditionMap.keySet();
		Iterator<String> conditionIt = conditionSet.iterator();
		int j = 0;
		while (conditionIt.hasNext()) {
			String name = conditionIt.next();
			if (j == 0) {
				sql.append(" where " + name + " = '" + conditionMap.get(name) + "'");
				j++;
				continue;
			}
			sql.append(" and " + name + " = '" + conditionMap.get(name) + "'");
		}

		Connection conn = getConnection();
		try {
			Statement st = conn.createStatement();
			int count = st.executeUpdate(sql.toString());
			return count;
		} catch (SQLException e) {
			System.out.println("-----------数据库出错!-----------");
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				System.out.println("-----------关闭conn出错-----------");
				e.printStackTrace();
			}
		}
		return 0;
	}

	/**
	 * 查询语句
	 * 
	 * @author： wangqiang
	 * @日期：2016-5-20 下午1:26:40
	 * @param tableName 表名
	 * @param targetNameList 查询字段名
	 * @param conditionMap 查询条件
	 * @return
	 */
	public Map<String, String> select(String tableName, List<String> targetNameList, Map<String, String> conditionMap) {

		//声明sql语句
		String sql = "select ";

		//将查询目标字段放入sql语句
		if (targetNameList != null && targetNameList.size() > 0) {
			for (int i = 0; i < targetNameList.size(); i++) {
				if (i == 0) {
					sql = sql + targetNameList.get(i);
					continue;
				}
				sql = sql + " , " + targetNameList.get(i);
			}
		}

		//补齐中间的sql语句
		sql = sql + " from " + tableName + " where ";

		//将查询条件放入sql语句
		if (conditionMap != null && conditionMap.size() > 0) {
			Set<String> keySet = conditionMap.keySet();
			Iterator<String> it = keySet.iterator();
			int i = 0;
			while (it.hasNext()) {
				String key = it.next();
				if (i == 0) {
					sql += key + " = " + conditionMap.get(key);
					i++;
					continue;
				}
				sql += " and " + key + " = " + conditionMap.get(key);
			}
		}

		//取得数据库连接,并进行查询操作
		Connection conn = getConnection();
		try {
			Statement st = conn.createStatement();
			ResultSet resultSet = st.executeQuery(sql);
			Map<String, String> resultMap = new HashMap<String, String>();
			while (resultSet.next()) {
				for (int i = 0; i < targetNameList.size(); i++) {
					String key = targetNameList.get(i);
					String values = resultSet.getString(key);
					resultMap.put(key, values);
				}
			}
			return resultMap;

		} catch (SQLException e) {
			System.out.println("\n-----------数据库操作出错!-----------");
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				System.out.println("-----------关闭数据库连接出错-----------");
				e.printStackTrace();
			}
		}

		return null;

	}
}
